CREATE PROC spcmsCustomer_GetReceivedMoneyInfo
@OrderCode nvarchar(128) = null
AS

SELECT cus.FullName,cus.UserName,cus.CustomerCode,cus.Phone,arm.ReceivedValue FROM cmsCustomer cus,cmsAccountReceiveMoney arm
WHERE cus.Id = arm.ToCustomerId AND arm.OrderCode = @OrderCode

GO

CREATE PROC spcmsOrder_FilterAdvance_History   
@CustomerId int,      
@PaymentMethod nvarchar(128) = null,      
@AccountCode nvarchar(128) = null,      
@OrderBy nvarchar(128) = null,      
@SystemStatus nvarchar(128) = null,    
@OrderTransactionStatus nvarchar(128) = null,  
@StatusNotIn nvarchar(256) = null,
 @Type nvarchar(128),      
 @SearchDate datetime,      
 @FromDate datetime,      
 @ToDate datetime    
AS      
BEGIN      
 DECLARE @sql nvarchar(max);      
  DECLARE @nullDate datetime = '1900-1-1'; 
 DECLARE @order nvarchar(128) = 'Id desc';      
 SET @sql = 'SELECT *,(SELECT top 1 SystemStatusText FROM cmsSytemStatus WHERE SystemStatus = cmsOrders.SystemStatus) AS SystemStatusText,    
       (SELECT top 1 TransactionStatus FROM cmsTransactionStatus WHERE TransactionStatusCode = cmsOrders.TransactionOrderStatus) AS TransactionStatusText,    
       (SELECT top 1 PaymentText FROM cmsPaymentMethod WHERE PaymentCode = cmsOrders.PaymentMethod) AS PaymentText    
       FROM cmsOrders WHERE 1 = 1 ';      
 IF @CustomerId > 0      
  SET @sql += ' AND CustomerId = ' + CONVERT(nvarchar,@CustomerId) + '';      
 --ELSE IF @CustomerId = -1      
 --SET @sql += ' AND (CustomerId EXISTS(SELECT Id from cmsCustomer where IsVangLai = 0))';      
 IF @PaymentMethod <> ''      
  SET @sql += ' AND PaymentMethod = ''' + @PaymentMethod + '''';      
 IF @AccountCode <> ''       
  SET @sql += ' AND AccountCode = ''' + @AccountCode + '''';      
 IF @SystemStatus <> ''   
  SET @sql += ' AND SystemStatus = ''' + @SystemStatus + '''';      
 IF @OrderTransactionStatus <> ''     
  SET @sql += ' AND TransactionOrderStatus = ''' + @OrderTransactionStatus + '''';  
  IF @Type = 'date'      
 BEGIN      
  IF DATEDIFF(day,@nullDate,@SearchDate) > 0      
   SET @sql = @sql + ' AND datediff(day,CreateDate,''' + CONVERT(nvarchar(100),@SearchDate) + ''')=0 '      
 END      
 ELSE IF @Type = 'fromdate'      
 BEGIN      
  IF DATEDIFF(day,@nullDate,@FromDate) > 0 AND DATEDIFF(day,@nullDate,@ToDate) > 0      
  SET @sql = @sql + ' AND (datediff(day,CreateDate,''' + CONVERT(nvarchar(100),@FromDate) + ''') <= 0 AND datediff(day,CreateDate,''' + CONVERT(nvarchar(100),@ToDate) + ''') >= 0) '       
 END          
 IF @OrderBy <> ''      
  SET @order =  @OrderBy;      
 SET @sql += ' ORDER BY ' + @order;      
 print(@sql);      
 Exec(@sql);      
END